﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using Silverlight.Training.Server.Common;
using Silverlight.Training.Server.Common.Contracts;
using Silverlight.Training.Server.Common.Feedbacks;
using Silverlight.Training.Server.Interfaces;

namespace Silverlight.Training.Server.DAOs
{
    public class EmployeeDAO : IEmployeeDAO
    {
        /// <summary>
        /// This method aims to get all employees from database
        /// </summary>
        /// <returns>
        /// A Feedback oject containing all employees
        /// </returns>        
        public CollectionFeedback<Employee> GetCollection()
        {
            try
            {
                SqlConnection connection = new SqlConnection(ServiceConstants.ConnectionString);
                SqlCommand command = new SqlCommand("GetEmployeeCollection", connection);
                command.CommandType = CommandType.StoredProcedure;
                SqlDataAdapter adapter = new SqlDataAdapter(command);
                DataTable dt = new DataTable("Table");
                adapter.Fill(dt);
                List<Employee> list = new List<Employee>();
                if (dt.Rows.Count != 0)
                {
                    foreach (DataRow dataRow in dt.Rows)
                    {
                        var result = new Employee
                        {
                            ID = Convert.ToInt32(dataRow["ID"]),
                            Name = Convert.ToString(dataRow["Name"]),
                            JobTitle = new JobTitle
                            {
                                ID = Convert.ToInt32(dataRow["JobTitleID"]),
                                TitleName = Convert.ToString(dataRow["TitleName"]),
                            },
                            Department = new Department
                            {
                                ID = Convert.ToInt32(dataRow["DepartmentID"]),
                                DeptName = Convert.ToString(dataRow["DeptName"]),
                            },
                            EmployeeType = new EmployeeType
                            {
                                ID = Convert.ToInt32(dataRow["EmployeeTypeID"]),
                                TypeName = Convert.ToString(dataRow["TypeName"]),
                            }
                        };

                        list.Add(result);
                    }
                }
                return new CollectionFeedback<Employee>("Get employee collection successfully!", true, list);
            }
            catch (SqlException e)
            {
                return new CollectionFeedback<Employee>("Get employee collection unsuccessfully! " + e.Message, false, null); ;
            }
            catch (Exception e)
            {
                return new CollectionFeedback<Employee>("Get employee collection unsuccessfully! " + e.Message, false, null); ;
            }
        }

        /// <summary>
        /// This method aims to get detailed information of an employee based on its ID.
        /// </summary>
        /// <param name="employeeID">
        /// The ID of the desired employee
        /// </param>
        /// <returns>
        /// A Feedback object containing a detailed employee.
        /// </returns>
        public SingleFeedback<Employee> GetSingle(int employeeID)
        {
            try
            {
                SqlConnection connection = new SqlConnection(ServiceConstants.ConnectionString);
                SqlCommand command = new SqlCommand("GetSingleEmployee", connection);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.AddWithValue("@employeeID", employeeID);

                SqlDataAdapter adapter = new SqlDataAdapter(command);
                DataTable dt = new DataTable("Table");
                adapter.Fill(dt);
                if (dt.Rows.Count == 1)
                {
                    foreach (DataRow dataRow in dt.Rows)
                    {
                        var result = new Employee
                        {
                            ID = Convert.ToInt32(dataRow["ID"]),
                            Name = Convert.ToString(dataRow["DeptName"]),
                            Description = Convert.ToString(dataRow["Description"]),
                            JobTitle = new JobTitle
                            {
                                ID = Convert.ToInt32(dataRow["JobTitleID"]),
                                TitleName = Convert.ToString(dataRow["TitleName"]),
                            },
                            Department = new Department
                            {
                                ID = Convert.ToInt32(dataRow["DepartmentID"]),
                                DeptName = Convert.ToString(dataRow["DeptName"]),
                            },
                            EmployeeType = new EmployeeType
                            {
                                ID = Convert.ToInt32(dataRow["EmployeeTypeID"]),
                                TypeName = Convert.ToString(dataRow["TypeName"]),
                            }
                        };

                        return new SingleFeedback<Employee>("Get employee details  successfully!", true, result);
                    }
                }
                return new SingleFeedback<Employee>("Get employee details unsuccessfully! Invalid reult.", false, null);
            }
            catch (SqlException e)
            {
                return new SingleFeedback<Employee>("Get employee details unsuccessfully! " + e.Message, false, null);
            }
            catch (Exception e)
            {
                return new SingleFeedback<Employee>("Get employee details unsuccessfully! " + e.Message, false, null);
            }
        }

        /// <summary>
        /// This method aims to insert a new object into database
        /// </summary>
        /// <param name="data">
        /// The Employee object to be inserted
        /// </param>
        /// <returns>
        /// A Feedback object containing information about 
        ///    - inserting result and 
        ///    - the ID of new object if success or -1
        /// </returns>
        public SingleFeedback<int> Insert(Employee data)
        {
            return null;
        }

        /// <summary>
        /// This method aims to update an existing record in database
        /// </summary>
        /// <param name="data">
        /// The Employee object to be updated
        /// </param>
        /// <returns>
        /// A Feedback object containing information about updating result
        /// </returns>
        public Feedback Update(Employee data)
        {
            return null;
        }

        /// <summary>
        /// This method aims to delete an existing record in database
        /// </summary>
        /// <param name="data">
        /// The Employee object to be deleted
        /// </param>
        /// <returns>
        /// A Feedback object containing information about deleting result
        /// </returns>
        public Feedback Delete(Employee data)
        {
            return null;
        }

        public CollectionFeedback<Employee> Search<TCriteria>(TCriteria criteria)
        {
            throw new NotImplementedException();
        }

        public CollectionFeedback<Employee> Search(Employee criteria)
        {
            throw new NotImplementedException();
        }
    }
}
